
package com.be.beadmin.product.repository;

import com.be.beadmin.product.dto.output.ActualTaskWorkHour;
import com.be.beadmin.product.entity.WorkingHoursCount;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.io.DataOutput;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
* @author zjm
* @date 2023-12-08
**/
public interface WorkingHoursCountRepository extends JpaRepository<WorkingHoursCount, String>, JpaSpecificationExecutor<WorkingHoursCount> {

    WorkingHoursCount findWorkingHoursCountByIdAndDelete(String id, Integer delete);


    WorkingHoursCount findWorkingHoursCountByPersonnelIdAndDateAndDelete(String id, Date date, Integer delete);
    WorkingHoursCount findWorkingHoursCountById(String id);


    @Query(value = "SELECT\n" +
            "    `name` AS userName,\n" +
            "    DATE_FORMAT(`manufact_start_date`, '%Y-%m') AS month,\n" +
            "    SUM(`sum_work_hours`) AS workHours\n" +
            "FROM\n" +
            "    (SELECT  u.`name` , r.* FROM (SELECT who.user_id, pmi.id process_id, pmi.manufact_start_date, pmi.manufact_end_date, who.sum_work_hours from t_om_wroking_hours_over_view who LEFT JOIN t_om_product_manufact_infor pmi on who.process_id = pmi.id) as r LEFT JOIN sys_user u ON r.user_id = u.id\n" +
            "\t\t) as wo\n" +
            "GROUP BY\n" +
            "    `name`,\n" +
            "    DATE_FORMAT(`manufact_start_date`, '%Y-%m');", nativeQuery = true)
    ActualTaskWorkHour sumPersonnelIdWorkingHours();

    @Query(value = "SELECT DISTINCT year_mon from (SELECT CONCAT(YEAR(manufact_start_date), MONTH(manufact_start_date)) AS year_mon\n" +
            "FROM t_om_user_work_hour_info WHERE manufact_start_date is not NULL\n" +
            "UNION\n" +
            "SELECT CONCAT(YEAR(manufact_end_date), MONTH(manufact_end_date)) AS year_mon\n" +
            "FROM t_om_user_work_hour_info WHERE manufact_end_date is not NULL\n" +
            "UNION\n" +
            "SELECT CONCAT(YEAR(manufact_start_date), MONTH(manufact_start_date)) AS year_mon\n" +
            "FROM t_op_work_hours_request_hst WHERE manufact_start_date is not NULL\n" +
            "UNION\n" +
            "SELECT CONCAT(YEAR(manufact_end_date), MONTH(manufact_end_date)) AS year_mon\n" +
            "FROM t_op_work_hours_request_hst WHERE manufact_end_date is not NULL) as un\n" +
            "ORDER BY year_mon",nativeQuery = true)
    List<String> findAllMonth();
    /**SELECT
     user_name,
     SUM(manufact_wh) AS manufact_wh,
     SUM(project_wh) AS project_wh,
     SUM(workorder_wh) AS workorder_wh
     FROM (
     SELECT
     user_name,
     -- 计算实际任务工时
     ROUND(SUM(TIMESTAMPDIFF(SECOND, start_date_sum, end_date_sum) / TIMESTAMPDIFF(SECOND, start_date, end_date) * manufact_workhour), 2) AS manufact_wh
     FROM (
     SELECT
     user_name,
     process_id,
     manufact_start_date AS start_date,
     COALESCE(manufact_end_date, NOW()) AS end_date,
     CASE
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = '202312' THEN manufact_start_date
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < '202312' THEN DATE_FORMAT('20231201', '%Y-%m-01 00:00:00')
     END AS start_date_sum,
     CASE
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = '202312' THEN COALESCE(manufact_end_date, NOW())
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > '202312' THEN DATE_FORMAT(LAST_DAY('20231201'),'%Y-%m-%d 23:59:59')
     END AS end_date_sum,
     manufact_workhour
     FROM t_om_user_work_hour_info
     WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= '202312'
     AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= '202312')
     ) AS task
     GROUP BY user_name
     ) AS t_produce_wh
     LEFT JOIN (
     -- 计算实际项目填报工时
     SELECT
     request_man,
     ROUND(SUM((DATEDIFF(end_date_sum, start_date_sum) + 1) / (DATEDIFF(end_date, start_date) + 1) * request_work_hours), 2) AS project_wh
     FROM (
     SELECT
     request_man,
     manufact_start_date AS start_date,
     COALESCE(manufact_end_date, NOW()) AS end_date,
     CASE
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = '202312' THEN manufact_start_date
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < '202312' THEN DATE_FORMAT('20231201', '%Y-%m-01 00:00:00')
     END AS start_date_sum,
     CASE
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = '202312' THEN COALESCE(manufact_end_date, NOW())
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > '202312' THEN DATE_FORMAT(LAST_DAY('20231201'),'%Y-%m-%d 23:59:59')
     END AS end_date_sum,
     request_work_hours
     FROM t_op_work_hours_request_hst
     WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= '202312'
     AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= '202312')
     AND request_type = 1
     ) AS project
     GROUP BY request_man
     ) AS t_project_wh ON t_produce_wh.user_name = t_project_wh.request_man
     LEFT JOIN (
     -- 计算实际工单填报工时
     SELECT
     request_man_wo,
     ROUND(SUM((DATEDIFF(end_date_sum, start_date_sum) + 1) / (DATEDIFF(end_date, start_date) + 1) * request_work_hours), 2) AS workorder_wh
     FROM (
     SELECT
     request_man AS request_man_wo,
     manufact_start_date AS start_date,
     COALESCE(manufact_end_date, NOW()) AS end_date,
     CASE
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = '202312' THEN manufact_start_date
     WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < '202312' THEN DATE_FORMAT('20231201', '%Y-%m-01 00:00:00')
     END AS start_date_sum,
     CASE
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = '202312' THEN COALESCE(manufact_end_date, NOW())
     WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > '202312' THEN DATE_FORMAT(LAST_DAY('202312'),'%Y-%m-%d 23:59:59')
     END AS end_date_sum,
     request_work_hours
     FROM t_op_work_hours_request_hst
     WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= '202312'
     AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= '202312')
     AND request_type = 0
     ) AS workorder
     GROUP BY request_man_wo
     ) AS t_workorder_wh ON t_produce_wh.user_name = t_workorder_wh.request_man_wo
     GROUP BY user_name;
     */

     /* *
     *
     * @param allMonth
     * @return
     */
    @Query(value =
            "SELECT \n" +
                    "    user_name,\n" +
                    "    SUM(manufact_wh) AS manufact_wh,\n" +
                    "    SUM(project_wh) AS project_wh,\n" +
                    "    SUM(workorder_wh) AS workorder_wh\n" +
                    "FROM (\n" +
                    "    SELECT \n" +
                    "        user_name,\n" +
                    "        ROUND(SUM(TIMESTAMPDIFF(SECOND, start_date_sum, end_date_sum) / TIMESTAMPDIFF(SECOND, start_date, end_date) * manufact_workhour), 2) AS manufact_wh\n" +
                    "    FROM (\n" +
                    "        SELECT \n" +
                    "            user_name,\n" +
                    "            process_id,\n" +
                    "            manufact_start_date AS start_date,\n" +
                    "            COALESCE(manufact_end_date, NOW()) AS end_date,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN manufact_start_date\n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN DATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
                    "            END AS start_date_sum,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN COALESCE(manufact_end_date, NOW())\n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN DATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
                    "            END AS end_date_sum,\n" +
                    "            manufact_workhour\n" +
                    "        FROM t_om_user_work_hour_info\n" +
                    "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1\n" +
                    "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= ?1)\n" +
                    "    ) AS task\n" +
                    "    GROUP BY user_name\n" +
                    ") AS t_produce_wh\n" +
                    "LEFT JOIN (" +
                    "    SELECT " +
                    "        request_man,\n" +
                    "        ROUND(SUM((DATEDIFF(end_date_sum, start_date_sum) + 1) / (DATEDIFF(end_date, start_date) + 1) * request_work_hours), 2) AS project_wh\n" +
                    "    FROM (\n" +
                    "        SELECT \n" +
                    "            request_man,\n" +
                    "            manufact_start_date AS start_date,\n" +
                    "            COALESCE(manufact_end_date, NOW()) AS end_date,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN manufact_start_date\n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN DATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
                    "            END AS start_date_sum,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN COALESCE(manufact_end_date, NOW())\n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN DATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
                    "            END AS end_date_sum,\n" +
                    "            request_work_hours\n" +
                    "        FROM t_op_work_hours_request_hst\n" +
                    "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1\n" +
                    "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= ?1) \n" +
                    "            AND request_type = 1\n" +
                    "    ) AS project\n" +
                    "    GROUP BY request_man\n" +
                    ") AS t_project_wh ON t_produce_wh.user_name = t_project_wh.request_man\n" +
                    "LEFT JOIN (\n" +
                    "    SELECT \n" +
                    "        request_man_wo,\n" +
                    "        ROUND(SUM((DATEDIFF(end_date_sum, start_date_sum) + 1) / (DATEDIFF(end_date, start_date) + 1) * request_work_hours), 2) AS workorder_wh\n" +
                    "    FROM (\n" +
                    "        SELECT \n" +
                    "            request_man AS request_man_wo,\n" +
                    "            manufact_start_date AS start_date,\n" +
                    "            COALESCE(manufact_end_date, NOW()) AS end_date,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN manufact_start_date\n" +
                    "                WHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN DATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
                    "            END AS start_date_sum,\n" +
                    "            CASE \n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN COALESCE(manufact_end_date, NOW())\n" +
                    "                WHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN DATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
                    "            END AS end_date_sum,\n" +
                    "            request_work_hours\n" +
                    "        FROM t_op_work_hours_request_hst\n" +
                    "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1\n" +
                    "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= '?1') \n" +
                    "            AND request_type = 0\n" +
                    "    ) AS workorder\n" +
                    "    GROUP BY request_man_wo\n" +
                    ") AS t_workorder_wh ON t_produce_wh.user_name = t_workorder_wh.request_man_wo\n" +
                    "GROUP BY user_name", nativeQuery = true)
    List<Map<String,Double>> statWorkHourAllUserByMonth(String Month,String MonthDay);

    @Query(value = "SELECT manufact_wh,project_wh, workorder_wh from   \n" +
            "-- 1. 实际任务工时\n" +
            "(SELECT \n" +
            "round(sum(TIMESTAMPDIFF(SECOND,start_date_sum,end_date_sum) / TIMESTAMPDIFF(SECOND,start_date,end_date) * manufact_workhour),2) as manufact_wh\n" +
            "from \n" +
            "(SELECT\n" +
            "            user_name,\n" +
            "            process_id,\n" +
            "            manufact_start_date AS start_date,\n" +
            "            COALESCE(manufact_end_date, NOW()) AS end_date,\t\n" +
            "\t\t\t\t\t\tCASE \n" +
            "  -- 当开始时间为当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN \n" +
            "\t  manufact_start_date\n" +
            "  -- 当开始时间小于当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN\n" +
            "\t\tDATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
            "END as start_date_sum,\n" +
            "\n" +
            "CASE \n" +
            "  -- 当结束为当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN \n" +
            "\t  COALESCE(manufact_end_date, NOW())\n" +
            "  -- 当结束时间大于当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN\n" +
            "\t\tDATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
            "END as end_date_sum,\n" +
            "\t\t\t\tmanufact_workhour\n" +
            "        FROM t_om_user_work_hour_info\n" +
            "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1 -- 选择指定月份\n" +
            "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= ?1\n" +
            "\t\t\t\t\t\tAND user_name = ?3 and is_delete = 0)) as workhour \n" +
            " ) as t_produce_wh\t\n" +
            ",\n" +
            "-- 2. 实际项目填报工时\n" +
            "\n" +
            "(SELECT \n" +
            "round(sum((DATEDIFF(end_date_sum,start_date_sum) + 1) / (DATEDIFF(end_date,start_date) + 1) * request_work_hours),2) as project_wh\n" +
            "from \n" +
            "(SELECT\n" +
            "            request_man,\n" +
            "            manufact_start_date AS start_date,\n" +
            "            COALESCE(manufact_end_date, NOW()) AS end_date,\t\n" +
            "\t\t\t\t\t\tCASE \n" +
            "  -- 当开始时间为当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN \n" +
            "\t  manufact_start_date\n" +
            "  -- 当开始时间小于当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN\n" +
            "\t\tDATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
            "END as start_date_sum,\n" +
            "\n" +
            "CASE \n" +
            "  -- 当结束为当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN \n" +
            "\t  COALESCE(manufact_end_date, NOW())\n" +
            "  -- 当结束时间大于当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN\n" +
            "\t\tDATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
            "END as end_date_sum,\n" +
            "\t\t\t\trequest_work_hours\n" +
            "        FROM t_op_work_hours_request_hst\n" +
            "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1 -- 选择指定月份\n" +
            "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= ?1) AND request_type = 1 and request_man = ?3 and is_delete = 0) as workhour \n" +
            "\t\t\t\t\t\t) as t_project_wh\n" +
            ",\n" +
            "\t\t\t\t\t\t\n" +
            "-- 3. 实际工单填报工时\n" +
            "(SELECT \n" +
            "round(sum((DATEDIFF(end_date_sum,start_date_sum) + 1) / (DATEDIFF(end_date,start_date) + 1) * request_work_hours),2) as workorder_wh\n" +
            "from \n" +
            "(SELECT\n" +
            "            request_man as request_man_wo,\n" +
            "            manufact_start_date AS start_date,\n" +
            "            COALESCE(manufact_end_date, NOW()) AS end_date,\t\n" +
            "\t\t\t\t\t\tCASE \n" +
            "  -- 当开始时间为当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') = ?1 THEN \n" +
            "\t  manufact_start_date\n" +
            "  -- 当开始时间小于当前月份\n" +
            "\tWHEN DATE_FORMAT(manufact_start_date, '%Y%m') < ?1 THEN\n" +
            "\t\tDATE_FORMAT(?2, '%Y-%m-01 00:00:00')\n" +
            "END as start_date_sum,\n" +
            "\n" +
            "CASE \n" +
            "  -- 当结束为当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') = ?1 THEN \n" +
            "\t  COALESCE(manufact_end_date, NOW())\n" +
            "  -- 当结束时间大于当前月份\n" +
            "\tWHEN DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') > ?1 THEN\n" +
            "\t\tDATE_FORMAT(LAST_DAY(?2),'%Y-%m-%d 23:59:59')\n" +
            "END as end_date_sum,\n" +
            "\t\t\t\trequest_work_hours\n" +
            "        FROM t_op_work_hours_request_hst\n" +
            "        WHERE DATE_FORMAT(manufact_start_date, '%Y%m') <= ?1 -- 选择指定月份\n" +
            "            AND (DATE_FORMAT(COALESCE(manufact_end_date, NOW()), '%Y%m') >= ?1) AND request_type = 0 and request_man = ?3 and is_delete = 0) as workhour \n" +
            ") as t_workorder_wh\t\t\n", nativeQuery = true)
    Map<String,Double> statWorkHourByUserAndMonth(String Month,String MonthDay,String userName);
}